# coding=utf-8
import redis
import time
from playwright.sync_api import sync_playwright
import pymysql.cursors
from parsel import Selector


db_host = '121.4.69.146'
db_port = 3306

conn = pymysql.connect(host=db_host,
                     user='root',
                     port=db_port,
                     password='xhhg@2020',
                     database='big_data')

cur = conn.cursor()
redis_con = redis.Redis(
    host='121.4.69.146', password='xhhg123456', port=6389, db=2, decode_responses=True)


def create_table(now_time):
    # 创建三个表
    create_sql_payment = f"CREATE TABLE If Not Exists `by_payment_information_{now_time}` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id', `cost_category` varchar(50) DEFAULT NULL COMMENT '缴费种类', `payment_amount` varchar(50) DEFAULT NULL COMMENT '缴费金额', `pay_date` datetime DEFAULT NULL COMMENT '缴费日期', `name_payer` varchar(50) DEFAULT NULL COMMENT '缴费人', `note_the_code` varchar(50) DEFAULT NULL COMMENT '票据代码', `ticket_number` varchar(50) DEFAULT NULL COMMENT '票据号码', `create_time` datetime DEFAULT NULL COMMENT '插入时间', `updata_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更改时间', `application_number` varchar(50) DEFAULT NULL COMMENT '申请号', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;"
    cur = conn.cursor()
    cur.execute(create_sql_payment)
    conn.commit()

    create_sql_annual = f"CREATE TABLE If Not Exists `annual_fee_information_{now_time}` (`id` int(11) NOT NULL AUTO_INCREMENT, `application_number` varchar(255) DEFAULT NULL, `applicant` varchar(255) DEFAULT NULL COMMENT '专利权人', `money_type` varchar(255) DEFAULT NULL, `money` varchar(255) DEFAULT NULL, `close_date` varchar(255) DEFAULT NULL, `agency` varchar(255) DEFAULT NULL COMMENT '代理机构', `pay_status` varchar(50) DEFAULT '0', `created_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `upload_inst` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;"
    cur.execute(create_sql_annual)
    conn.commit()

    create_sql_overdue = f"CREATE TABLE If Not Exists `overdue_fine_{now_time}` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id', `application_number` varchar(50) DEFAULT NULL COMMENT '申请号', `payment_start_time` datetime DEFAULT NULL COMMENT '滞纳金开始时间', `payment_end_time` datetime DEFAULT NULL COMMENT '滞纳金结束时间', `current_annual_fee` varchar(20) DEFAULT NULL COMMENT '当前年费金额', `late_fee` varchar(20) DEFAULT NULL COMMENT '应交滞纳金额', `total` varchar(20) DEFAULT NULL COMMENT '总计', `pay_status` int(5) DEFAULT NULL COMMENT '缴费状态', `create_time` datetime DEFAULT NULL COMMENT '插入时间', `updata_time` datetime DEFAULT NULL COMMENT '更改时间', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;"
    cur.execute(create_sql_overdue)
    conn.commit()
    print('@@成功创建%s三个目标表@@' % now_time)


def scripy(page, application_number, inst):
    page.click('xpath=//span[contains(text(),"费用信息")]')
    time.sleep(3)
    page.locator('//p[contains(text(),"费用信息")]').wait_for(timeout=20000)
    # 解析费用信息
    sqh = 'ZL' + application_number
    page_soup = Selector(page.content())
    # 已缴费信息
    yijiaofei_text_list = page_soup.xpath(
        '//p[text()="已缴费信息"]/../following-sibling::div[1]//table/tbody/tr').getall()
    yijiaofei_name_set = set()
    yijiaofei_list = []
    for yijiaofei_text in yijiaofei_text_list:
        yijiaofei_soup = Selector(yijiaofei_text)
        cost_category = yijiaofei_soup.xpath(
            '(//td)[1]/text()').extract_first()
        payment_amount = yijiaofei_soup.xpath(
            '(//td)[2]/text()').extract_first()
        pay_date = yijiaofei_soup.xpath('(//td)[3]/text()').extract_first()
        name_payer = yijiaofei_soup.xpath('(//td)[4]/text()').extract_first()
        note_the_code = yijiaofei_soup.xpath(
            '(//td)[5]/text()').extract_first()
        ticket_number = yijiaofei_soup.xpath(
            '(//td)[6]/text()').extract_first()
        yijiaofei_name_set.add(cost_category)
        rows = [cost_category, payment_amount, pay_date,
                name_payer, note_the_code, ticket_number]
        if rows:
            yijiaofei_list.append(rows)
            yijiao_sql = f'''insert into by_payment_information_0504(cost_category, payment_amount, pay_date, name_payer, note_the_code, ticket_number, create_time, application_number)
                values('{cost_category}', '{payment_amount}', '{pay_date}', '{name_payer}', '{note_the_code}', '{ticket_number}', NOW(), '{application_number}')'''
            try:
                cur = conn.cursor()
                cur.execute(yijiao_sql)
                conn.commit()
                print(yijiao_sql)
            except:
                print('@@数据插入数据库失败@@')
    print('##application_number:%s--已缴费信息入库完成##' % application_number)

    # 应缴费信息
    yinjiaofei_text_list = page_soup.xpath(
        '//p[text()="应缴费信息"]/../following-sibling::div[1]//table/tbody/tr').getall()
    yinjiaofei_list = []
    for yinjiaofei_text in yinjiaofei_text_list:
        yinjiaofei_soup = Selector(yinjiaofei_text)
        money_type = yinjiaofei_soup.xpath('(//td)[1]/text()').extract_first()
        money = yinjiaofei_soup.xpath('(//td)[2]/text()').extract_first()
        close_date = yinjiaofei_soup.xpath('(//td)[3]/text()').extract_first()
        pay_status = yinjiaofei_soup.xpath('(//td)[4]/text()').extract_first()
        rows = [money_type, money, close_date, pay_status]
        if rows:
            yinjiaofei_list.append(rows)
            if money_type not in yijiaofei_name_set:
                yinjiao_sql = f'''insert into annual_fee_information_0504 (application_number, money_type, money, close_date, pay_status, created_at)
              values('{application_number}', '{money_type}', '{money}', '{close_date}', '{pay_status}', NOW())'''
                try:
                    cur = conn.cursor()
                    cur.execute(yinjiao_sql)
                    conn.commit()
                    print(yinjiao_sql)
                except:
                    print('@@数据插入数据库失败@@')
    print('@@application_number:%s--应缴费信息入库完成@@' % application_number)

    # 滞纳费信息
    zhinafei_text_list = page_soup.xpath(
        '//p[text()="滞纳金信息"]/../following-sibling::div[1]//table/tbody/tr').getall()
    zhinafei_list = []
    for zhinafei_text in zhinafei_text_list:
        zhinafei_soup = Selector(zhinafei_text)
        date_str = zhinafei_soup.xpath('(//td)[1]/text()').extract_first()
        date_list = date_str.split('到')
        date_list = [item.replace(
            '年', '-').replace('月', '-').replace('日', '') for item in date_list]
        start_date = date_list[0]
        end_date = date_list[1]
        current_annual_fee = zhinafei_soup.xpath(
            '(//td)[2]/text()').extract_first()
        late_fee = zhinafei_soup.xpath('(//td)[3]/text()').extract_first()
        total = zhinafei_soup.xpath('(//td)[4]/text()').extract_first()
        rows = [start_date, end_date, current_annual_fee, late_fee, total]
        if rows:
            zhinafei_list.append(rows)
            zhinajin_sql = f'''insert into overdue_fine_0504(application_number, payment_start_time, payment_end_time, current_annual_fee, late_fee, total, create_time, updata_time) values('{application_number}','{start_date}','{end_date}','{current_annual_fee}','{late_fee}','{total}',NOW(),NOW())'''
            try:
                cur = conn.cursor()
                cur.execute(zhinajin_sql)
                conn.commit()
                print(zhinajin_sql)
            except:
                print('@@数据插入数据库失败@@')
    print('**application_number:%s--已缴费信息入库完成**' % application_number)

    if port == '信合红谷接口查询' and yinjiaofei_list == [] and yijiaofei_list == [] and zhinafei_list == []:
        try:
            redis_con.sadd("协和端口查询", application_number)
        except:
            time.sleep(5)
            redis_con.sadd("协和端口查询", application_number)
        # 使用协和端口查询
        print('@@xhhg未查到相关信息，使用xh进行查询@@')
    elif port == '协和端口查询' and yinjiaofei_list == [] and yijiaofei_list == [] and zhinafei_list == []:
        try:
            redis_con.sadd("外部接口查询", application_number)
        except:
            time.sleep(5)
            redis_con.sadd("外部接口查询", application_number)
        print('@@xh端口未查到相关信息，使用外部接口查询@@')
    elif port == '外部接口查询' and yinjiaofei_list == [] and yijiaofei_list == [] and zhinafei_list == []:
        try:
            redis_con.sadd("全部接口未查到", application_number)
        except:
            time.sleep(5)
            redis_con.sadd("全部接口未查到", application_number)
        print('@@全部接口均未查到@@')


# def select_sqh():
#     sql = 'SELECT application_number FROM xhhg_pantent_status_0428'
#     cur.execute(sql)
#     data = cur.fetchall()
#     have_already_done = set([sqh[0].replace("ZL", '') for sqh in data])
#     return have_already_done


def run(inst, port):
    # 使用账号密码登录
    port_code_dict = {'xhhg': (r'42264',r'Xhhg2019@'), 'xh': ('12420000420007938Y','XHYYkyc85755457@'), 'wb': ('13283766953','Ws123456+++')}
    user,passwd = port_code_dict.get(inst)
    with sync_playwright() as p:
        # browser = p.chromium.launch(headless=False)
        browser = p.firefox.launch(headless=False)
        context = browser.new_context()
        context.set_default_timeout(120000)
        context.set_default_navigation_timeout(120000)
        page = context.new_page()
        page.set_default_timeout(120000)
        page.set_default_navigation_timeout(120000)
        page.goto('https://tysf.cponline.cnipa.gov.cn/am/#/user/login')
        page.wait_for_load_state()
        if inst=='xhhg':
            page.click('xpath=//span[text()="代理机构登录"]')
            time.sleep(1)
            page.click('xpath=//input[@placeholder="代理机构代码"]')
            time.sleep(0.5)
            page.fill('xpath=//input[@placeholder="代理机构代码"]', user)
            time.sleep(1)
        elif inst=='xh':
            page.click('xpath=//span[text()="法人登录"]')
            time.sleep(1)
            page.click('xpath=//input[@placeholder="统一社会信用代码"]')
            time.sleep(0.5)
            page.fill('xpath=//input[@placeholder="统一社会信用代码"]', user)
            time.sleep(1)
        elif inst=='wb':
            page.click('xpath=//span[text()="自然人登录"]')
            time.sleep(1)
            page.click('xpath=//input[@placeholder="手机号/证件号码"]')
            time.sleep(0.5)
            page.fill('xpath=//input[@placeholder="手机号/证件号码"]', user)
            time.sleep(1)
        page.click('xpath=//input[@placeholder="请输入密码"]')
        time.sleep(0.5)
        page.fill('xpath=//input[@placeholder="请输入密码"]', passwd)
        time.sleep(0.5)
        page.click('xpath=//span[text()="登录"]')
        time.sleep(15)
        # input('%s-滑动验证登录网站：' % i)
        page.goto('https://cpquery.cponline.cnipa.gov.cn/chinesepatent/index')
        page.wait_for_load_state()
        time.sleep(5)
        
        if '关于中国及多国专利审查信息查询系统新旧系统双运行模式的通知' in page.content():
            page.click('xpath=//span[@class="q-btn__content text-center col items-center q-anchor--skip justify-end row"]')
        redis_con = redis.Redis(host='121.4.69.146', password='xhhg123456',
                                port=6389, db=2, decode_responses=True)     # 连接redis数据库
        time.sleep(2)
        while redis_con.scard(port):
            sqh = redis_con.spop(port).replace("ZL", '')
            print(f'@@开始查询专利号：{sqh}@@')
            start_time = time.time()
            zhuanli_url = r"https://cpquery.cponline.cnipa.gov.cn/detail/index?zhuanlisqh=%s&anjianbh" % sqh
            page.goto(zhuanli_url)
            page.wait_for_load_state('networkidle')
            # time.sleep(5)
            scripy(page, sqh, inst)
            end_time = time.time()
            print(f"本次耗时: {end_time-start_time}")
            need_time = round((end_time - start_time) *
                              redis_con.scard(port) / 60, 1)
            print(
                f"开始抓取'{sqh}'的信息， 剩余{redis_con.scard(port)}个，大约还需要{need_time}分钟")
            print("\n")


if __name__ == '__main__':
    # now_time='0504'
    # create_table(now_time=now_time)
    port_dict = {'xhhg': '信合红谷接口查询', 'xh': '协和端口查询', 'wb': "外部接口查询"}
    inst = 'wb'
    port =port_dict.get(inst)
    s_time = time.time()
    run(inst,port)
    conn.close()
    en_time = time.time()
    print(f"本次耗时: {en_time - s_time}")
